Best Of
Re: IF INDEX MATCH yielding INVALID DATA or NO MATCH
You never completed the logical test in your if() statement, right now you are saying are telling it to index to a person's name but not telling it what to do as far as a logical test goes. "=if ( "Frank", "yes", "no")"…. is Frank true or false?
I'd recommend the following instead:
=IF(countifs({CT Certs Range 1}, [Instructor Name]@row)>0, "yes", "no")
here you are saying "=if( "Frank" shows up more than 0 times, "yes", "no")"
Also, just fyi, I'd do it as a countifs rather than a simple countif because you may want to add more criteria later and the difference in performance is unlikely to be noticed. But, for the record, countif() would actually be marginally better than countifs().
Jgorsich
Re: Saving a "Form" for later use without submitting as of yet
Hello,
I am curious to know if there's any option to add "Save and continue" option into the form so the same user can finish the submission later or eventually to pass over to another user who would finish and submit the form?
Thank you,
Cvetelina Cheresharska
Lina
Re: Add Column Description to Update Request
I like using the Request Updated so that a form is sent to the person where I am requesting an update. I would like to add some help test to the fields when it is sent. This is the same text that I might put in the column description. My update request is to include the Column Description under the name field. This way when the user gets the Update Request form, I can also provide a little bit of help as they fill out the auto-generated form.
Re: Formula for averaging days
Odd, it worked on this side when I tested it.
Per Formula error messages | Smartsheet Learning Center:
#UNPARSEABLE
Cause
The formula has a problem which prevents it from being parsed and interpreted. This can happen for many reasons, such as misspelling, incomplete operators, using the wrong case for a column name, or using single quotes instead of double quotes.
Resolution
Ensure that all column names are spelled correctly in cell references, operators are being used correctly, and any text strings in the formula are surrounded by double quotes (" ").
Looking at the below the actual structure looks okay. So, I'd recommend 2 things - first, modify your "Days Duration (Received to Listed)" column formula to include an iferror() statement to force anything non-numeric to be text (I like just "", that way it is empty visually - but any text will work) instead of an error message. Error messages in referenced columns tend to break things and error traps are a good practice in general. Second, double check all your column names and types. This formula would expect the following column names and types:
Days Duration (Received to Listed) - Text/Number
Date Received - Date
=avg(collect([Days Duration (Received to Listed)]:[Days Duration (Received to Listed)],[Date Received]:[Date Received],>=date(2025,1,1),[Date Received]:[Date Received],<=date(2025,1,31),[Days Duration (Received to Listed)]:[Days Duration (Received to Listed)],>0)
Jgorsich
Re: Count Multiple Values in the Same Column
Hello @Almrie217
You're almost there! You're just missing some formula logic on your OR statement using "@cell".
Try this:
=COUNTIFS([PRODUCTION STAGE]:[PRODUCTION STAGE],OR(@cell="DELIVERED",@cell="PRODUCTION"),TYPE:TYPE,OR(@cell="Mug",@cell="Cup"))
If you'll be using a multi-select dropdown column, you need to utilize the HAS function.
=COUNTIFS([PRODUCTION STAGE]:[PRODUCTION STAGE],OR(@cell="DELIVERED",@cell="PRODUCTION"),TYPE:TYPE,OR(HAS(@cell,"Mug"),HAS(@cell,"Cup")))
Note that the HAS function checks if the cell has that value selected. So for example, you have 3 rows that has either "Mug", "Cup", or both "Mug" and "Cup" in a cell. It will not count as 2 values when a cell contains both Mug and Cup. I hope I makes sense.
Melissa Yamada
Re: Display hours in a day on gantt and agenda view
My team had an idea to start logging machine times. We have a machine that scans things and we wanted a cleaner way to start scheduling people to use it.
Essentially people would go to the sheet, log the time they would be using it (name, scan reason, how long scan takes - scan time would essentially be log time) so people would know at this specific time the machine is being used. Logs would be in Hours/Minutes not days/weeks.
Project Management template would be perfect for this but it only does days/weeks. The calendar would be huge for this if it allowed for hours/minutes
Don't know if smartsheet currently supports this but I can't find a way around it
santiago.rendon
Sheet opening with filter but there's no default filter
Why is a Smartsheet opening with a specific filter on? Whenever I open a specific Smartsheet, it opens with this one specific filter on, even if I save it with no filter on. This also happens if I click the link in a notification email. I've checked to see if it was set up as the default, but the Default View is set to Filter = None. I've deleted the filter and created a new one and this still happens. It's also the last filter created on this sheet, although I was not the original creator. I've also cleared my cache, because this is not happening to my co-worker.
clair11
Re: Is there a way to turn off automatically applying the last filter used when reloading a Smartsheet?
This is something that my team has just started experiencing. It is defaulting to the last filter they used rather than to the default for the sheet (in this case, no filter). Is there a way we can "turn off" the last filter they used?
meghan.hale66546